﻿CREATE PROCEDURE [dbo].[proc_Customer_DProtect_TongJi]
(
	@CompanyId int,
	@DepartmentId int,
	@Stext nvarchar(50),
	@StartDate nvarchar(50),
	@EndDate nvarchar (50)
)
AS
Begin
	Declare @sRq Datetime,@eRq Datetime
	Set @sRq = Convert(Datetime,@StartDate+' 00:00:00')
	Set @eRq = Convert(Datetime,@EndDate+' 23:59:59')

	Declare @bmbh_T varchar(30)
	Select @bmbh_T=bmbh From department Where id=@DepartmentId

	--取该部门所有的员工
	;WITH List2 As (
		Select e.UserName,e.EName,
		IsNull(d.DName,'') As DName From Employee e
		Left Join Department d On e.DepartmentId=d.id
		Where e.CompanyId=@CompanyId And e.[State]<>5 And Left(d.bmbh,Len(@bmbh_T))=@bmbh_T
		And (@Stext='' Or e.EName Like '%' + @SText + '%')
	),
	List1 As(
		Select ProtectName,Count(0) As Shl1,0 As Shl2
		From Customer
		Where CompanyId=@CompanyId And ProtectDate>=@sRq And ProtectDate<=@eRq
		And ProtectId=2
		Group By ProtectName
		Union All
		Select ProtectName,0 As Shl1,Count(0) As Shl2
		From Customer
		Where CompanyId=@CompanyId And ProtectDate>=@sRq And ProtectDate<=@eRq
		 And ProtectId=4
		Group By ProtectName
	),
	List As (Select a.DName As DeptName,a.EName As ProtectName,
		Sum(IsNull(b.Shl1,0)) As Shl1,Sum(IsNull(b.Shl2,0)) As Shl2
		From List2 a Left Join List1 b On a.UserName=b.ProtectName
		Group By a.DName,a.EName
	)

	Select *,(Select Count(0) From List) As RecordCount
	From List Order By Shl1 Desc,Shl2 Desc
End